﻿using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Kay.OfficeHandler
{
    /// <summary>
    /// Excel 帮助器
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 使用 DataTable 导出到 Excel
        /// </summary>
        /// <param name="info">导出实体</param>
        /// <returns>Excel 文件流或 Null</returns>
        public static MemoryStream DataTableToExcel(ExportInfo info)
        {
            MemoryStream ms = null;
            if (info == null)
                return null;

            try
            {
                var workbook = new HSSFWorkbook();
                var sheet = workbook.CreateSheet(info.Title);
                var rowCount = info.Source.Rows.Count;
                var colCount = info.Source.Columns.Count;

                var titleRow = sheet.CreateRow(0);
                for (var i = 0; i < colCount; i++)
                {
                    var titleCell = titleRow.CreateCell(i);
                    titleCell.SetCellValue(info.Source.Columns[i].ColumnName);
                }

                for (var i = 0; i < rowCount; i++)
                {
                    var dataRow = sheet.CreateRow(i + 1);
                    for (var j = 0; j < colCount; j++)
                    {
                        var dataCell = dataRow.CreateCell(j);
                        dataCell.SetCellValue(info.Source.Rows[i][j].ToString());
                    }
                }

                ms = new MemoryStream();
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }
            catch
            {
                return ms;
            }

            return ms;
        }

        /// <summary>
        /// 使用 DataTable 导出到 Excel
        /// <para>并为单元格保留原有数据类型</para>
        /// </summary>
        public static MemoryStream DataTableToExcelWithType(ExportInfo entity)
        {
            MemoryStream ms = null;
            if (entity == null)
            {
                return null;
            }
            try
            {
                // 创建sheet页
                var workbook = new HSSFWorkbook();
                var sheet = workbook.CreateSheet(entity.Title);
                var rowCount = entity.Source.Rows.Count;
                var colCount = entity.Source.Columns.Count;

                // 添加标题行
                var titleRow = sheet.CreateRow(0);
                for (var i = 0; i < colCount; i++)
                {
                    var titlecell = titleRow.CreateCell(i);
                    titlecell.SetCellValue(entity.Source.Columns[i].ColumnName);
                }

                // 写入数据
                for (var i = 0; i < rowCount; i++)
                {
                    var dataRow = sheet.CreateRow(i + 1);
                    for (var j = 0; j < colCount; j++)
                    {
                        var dataCell = dataRow.CreateCell(j);
                        var colType = entity.Source.Columns[j].DataType;
                        if (colType == typeof(int) || colType == typeof(double) || colType == typeof(float) ||
                            colType == typeof(decimal) || colType == typeof(short) || colType == typeof(long) ||
                            colType == typeof(uint) || colType == typeof(ulong) || colType == typeof(ushort))
                        {
                            dataCell.SetCellType(CellType.Numeric);
                            double value;
                            var r = double.TryParse(entity.Source.Rows[i][j].ToString(), out value);
                            if (r)
                            {
                                dataCell.SetCellValue(value);
                            }
                            else
                            {
                                dataCell.SetCellValue(entity.Source.Rows[i][j].ToString());
                            }
                        }
                        else
                        {
                            dataCell.SetCellValue(entity.Source.Rows[i][j].ToString());
                        }
                    }
                }
                ms = new MemoryStream();
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }
            catch
            {
                return ms;
            }
            return ms;
        }
    }
}
/**********          Web 导出参考          **********/
/**************************************************************************************************
public ActionResult EmportExcel(string title, string content)
{
    var month = DateTime.Now.ToString("yyyy-MM");
    var userInfo = LoginUser as TbmUserLoginInfo;
    if (userInfo == null) return null;
    var fileSimpleName = userInfo.AttDeptName + "-" + month + "-信息发布管理表.xls";
    var searchModel = new TjlNewsReleaseSearchModel();
    if (!string.IsNullOrWhiteSpace(title))
    {
        searchModel.Title = title;
    }
    if (!string.IsNullOrWhiteSpace(content))
    {
        searchModel.News = content;
    }
    var list = TjlNewsReleaseBll.GetByConditionNoPage(searchModel);

    var dt = HRMHelper.ToDataTable(list);

    #region 修改列的名称和删除不需要的列
    //dt.Columns.Remove("GUID");
    //dt.Columns.Remove("MainRecordId");
    //dt.Columns.Remove("VerifyID");
    //dt.Columns.Remove("VerifyNo");
    //dt.Columns.Remove("DepartmentId");
    //dt.Columns.Remove("OperatorId");
    //dt.Columns.Remove("OtherInfo1");
    //dt.Columns.Remove("OtherInfo2");
    //dt.Columns["VerifyType"].ColumnName = "审核类型";
    //dt.Columns["VerifyState"].ColumnName = "审核状态";
    //dt.Columns["VerifyOpinion"].ColumnName = "审核意见";
    //dt.Columns["VerifyName"].ColumnName = "审核名称";
    //dt.Columns["VerifyMonth"].ColumnName = "审核月份";
    //dt.Columns["VerifyDate"].ColumnName = "审核日期";
    //dt.Columns["DepartmentName"].ColumnName = "科室名称";
    //dt.Columns["RecordState"].ColumnName = "记录状态";
    //dt.Columns["ComputerName"].ColumnName = "计算机名称";
    //dt.Columns["LoginDateTime"].ColumnName = "登陆时间";
    //dt.Columns["LastUpdateTime"].ColumnName = "更新时间";
    #endregion

    var printEntity = new PrintEntity
    {
        DeptName = userInfo.AttDeptName,
        DtSource = dt,
        Month = month,
        PrintTitle = " 信息发布管理表 ",
        OpeName = userInfo.EmpName,
        FileSavePath = Server.MapPath("/bin/")
    };

    var ms = ExcelHelper.DataTableToExcelWithNumber(printEntity);
    if (ms == null) return null;
    ms.Seek(0, SeekOrigin.Begin);
    return File(ms, "application/vnd.ms-excel", fileSimpleName);
}
**************************************************************************************************/